** CoreLogic HPG for The Effects of Changes in Local Bank Health on Household Consumption
** by Daniel Cooper and Joe Peek, ReStat 2020
/* ////////////////////////////////////////////////////////////////////////// */
clear all
set more off
set maxvar 30000

* This code puts together HPI growth at the MSA and State level
* The output must merged with PSID uniques on a PSID terminal/enclave

* Note that the code is setup for the CoreLogic data used in the paper.  However
* there are publicly available state and MSA level house price data available from FHFA
* that could be utilized instead.  The data would just need to be setup in a similar way 
* to work with the existing code.


* The code is divided into two blocks for ease of execution  
scalar st = 0 // state level
scalar msa = 0 // msa level

* update the line below with the relevant path for running your analysis
local data "/shared/Joe_Peek/bankhealth_nocamels/data"

********************************************************************************
if st {
**********************************************
*load in Core Logic HPI state data 
use `data'/hpi_state.dta, clear

destring tier_code, replace
keep if tier_code == 11

*date variable
tostring as_of_mon_id, replace
g year = substr(as_of_mon_id, 1, 4)
g month = substr(as_of_mon_id, 5, 2)
destring year, replace
destring month, replace

*generate quarters
g qtr=1 if month==1 | month==2 | month==3 
replace qtr=2 if month==4 | month==5 | month==6
replace qtr=3 if month==7 | month==8 | month==9
replace qtr=4 if month==10 | month==11 | month==12

collapse (mean) home_price_index (first) state_name, by(state_code year qtr)

rename home_price_index hpi_state

*drop national
drop if state_name == "National"

*crosswalk; create PSID state codes
g st = 1 if state_name == "Alabama"
replace st = 2 if state_name == "Arizona"
replace st =3 if state_name == "Arkansas"
replace st = 4 if state_name == "California"
replace st = 5 if state_name == "Colorado"
replace st = 6 if state_name == "Connecticut"
replace st = 7 if state_name == "Delaware"
replace st = 8 if state_name == "District of Columbia"
replace st = 9 if state_name == "Florida"
replace st = 10 if state_name == "Georgia"
replace st = 11 if state_name == "Idaho"
replace st = 12 if state_name == "Illinois"
replace st = 13 if state_name == "Indiana"
replace st = 14 if state_name == "Iowa"
replace st = 15 if state_name == "Kansas"
replace st = 16 if state_name == "Kentucky"
replace st = 17 if state_name == "Louisiana"
replace st = 18 if state_name == "Maine"
replace st = 19 if state_name == "Maryland"
replace st = 20 if state_name == "Massachusetts"
replace st = 21 if state_name == "Michigan"
replace st = 22 if state_name == "Minnesota"
replace st = 23 if state_name == "Mississippi"
replace st = 24 if state_name == "Missouri"
replace st = 25 if state_name == "Montana"
replace st = 26 if state_name == "Nebraska"
replace st = 27 if state_name == "Nevada" 
replace st = 28 if state_name == "New Hampshire"
replace st = 29 if state_name == "New Jersey"
replace st = 30 if state_name == "New Mexico"
replace st = 31 if state_name == "New York"
replace st = 32 if state_name == "North Carolina"
replace st = 33 if state_name == "North Dakota"
replace st = 34 if state_name == "Ohio"
replace st = 35 if state_name == "Oklahoma"
replace st = 36 if state_name == "Oregon" 
replace st = 37 if state_name == "Pennsylvania"
replace st = 38 if state_name == "Rhode Island"
replace st = 39 if state_name == "South Carolina"
replace st = 40 if state_name == "South Dakota"
replace st = 41 if state_name == "Tennessee"
replace st = 42 if state_name == "Texas"
replace st = 43 if state_name == "Utah"
replace st = 44 if state_name == "Vermont"
replace st = 45 if state_name == "Virginia"
replace st = 46 if state_name == "Washington"
replace st = 47 if state_name == "West Virginia"
replace st = 48 if state_name == "Wisconsin"
replace st = 49 if state_name == "Wyoming"
replace st = 50 if state_name == "Alaska"
replace st = 51 if state_name == "Hawaii" 

drop state_name state_code
rename st state

*calculating the lagged yearly growth rate

*(Timing wise we use housing price growth between the quarter right before an interview, and the the previous 4 quarters.
* as discussed in the text.)

g date_quarter = yq(year, qtr)

tempfile hpi
save `hpi'

*deflating data: use PCE deflator in BH dataset 
	*want consistency with other data and BH deflator not fully updated 	
merge m:1 year using `data'/pce_bh_def
keep if _merge == 3 
drop _merge 

ren hpi_state hpi
g hpi_real = hpi/def

*calculate lagged yearly growth rate 
xtset state date_quarter
g hpgrowm_4qtr_st = (L.hpi_real - L5.hpi_real)/L5.hpi_real

drop date_quarter def

save `data'/corelogic_hpi_fin_15.dta, replace
}

********************************************************************************
if msa {
*Need to convert metrodivision codes in the hpi data to cbsa codes consistent with the PSID coding
*A crosswalk is provided-- we stick with 2013 consisetent codes, which were consisetent with the PSID data as of the 2015 release
* used in the paper 

import excel `data'/census_cbsa_2013_codes.xlsx, firstrow clear cellrange(A1:E1883)

drop CBSATitle

destring CBSACode MetroDivisionCode, replace

g cbsa_and_metrodiv = MetroDivisionCode
replace cbsa_and_metrodiv = CBSACode if cbsa_and_metrodiv==.

drop MetroDivisionCode

rename MetropolitanMicropolitanStatis  msa_type

g metro_dum = 0
replace metro_dum = 1 if msa_type == "Metropolitan Statistical Area"
drop msa_type

tempfile cbsa_metrodiv_crosswalk
save `cbsa_metrodiv_crosswalk'

** this dataset is from CoreLogic 
use `data'/hpi_dw_cbsa_201710.dta, clear

*we use the tier code 11 house price data from CoreLogic
destring cbsa_code tier_code, replace

rename cbsa_code cbsa_and_metrodiv

keep if tier_code==11

*merging in crosswalk to fix the CBSA codes that are metro division codes

merge m:m cbsa_and_metrodiv using `cbsa_metrodiv_crosswalk'

*all the non merges are MSA's in Puerto Rico, so we can drop them
drop if _merge==2

drop _merge

rename CBSACode CBSA

drop cbsa_and_metrodiv

*making year, month and quarter variables

tostring as_of_mon_id, replace

g year = substr(as_of_mon_id, 1, 4)
g month = substr(as_of_mon_id, 5, 2)

destring year month, replace

g qtr=1 if month==1 | month==2 | month==3 
replace qtr=2 if month==4 | month==5 | month==6
replace qtr=3 if month==7 | month==8 | month==9
replace qtr=4 if month==10 | month==11 | month==12


*making the data quarterly

collapse (mean) home_price_index metro_dum, by(CBSA year qtr)


*calculating the lagged yearly growth rate

*(As above, we use the housing price growth between the quarter right before an interview, and the the previous 4 quarters.)

g date_quarter = yq(year, qtr)

tempfile hpi
save `hpi'


*deflating data: use PCE deflator 

merge m:1 year using `data'/pce_bh_def
keep if _merge == 3 
drop _merge 

ren home_price_index hpi
g hpi_real = hpi/def

xtset CBSA date_quarter

g hpgrowm_4qtr_msa = (L.hpi_real - L5.hpi_real)/L5.hpi_real

drop date_quarter def 

save `data'/hpi_msa_15.dta, replace
}
